package com.azhoucode.util;

import com.azhoucode.constant.Constant;
import com.azhoucode.entity.ApplicationEntity;
import com.azhoucode.entity.Parameters;
import com.azhoucode.entity.TableNameAndType;

import javax.swing.*;
import java.sql.*;
import java.util.*;

public class DBUtils {

    public static Connection getConnection(ApplicationEntity applicationEntity) {
        Connection conn = null;
        String url = "";
        String driverClass = "";
        try {
            url = "jdbc:mysql://" + applicationEntity.getIp() + ":" + applicationEntity.getDbPort() + "/"
                    + applicationEntity.getDbName()
                    + "?connectTimeout=10000&socketTimeout=10000&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull";
            driverClass = "com.mysql.cj.jdbc.Driver";
            Class.forName(driverClass);
            DriverManager.setLoginTimeout(10);
            conn = DriverManager.getConnection(url, applicationEntity.getUsername(),
                    applicationEntity.getPassword());
        } catch (Exception e) {
            return null;
        }
        return conn;
    }


    /**
     * 获取表的主键id
     *
     * @param parameters
     * @param tableName
     * @return
     * @throws Exception
     */
    public static List<String> getPrimarykey(Parameters parameters, String tableName) throws Exception {
        List<String> primaryKeyList = new ArrayList<>(3);
        Connection connection = null;
        if (connection == null) {
            return null;
        }
        DatabaseMetaData metaData = connection.getMetaData();
        String userName = connection.getMetaData().getUserName();
        // 获取主键字段
        ResultSet primaryKeys = metaData.getPrimaryKeys(null, userName, tableName);
        //根据结果集元数据打印内容
        ResultSetMetaData pkmd = primaryKeys.getMetaData();
        while (primaryKeys.next()) {
            for (int i = 1; i <= pkmd.getColumnCount(); i++) {
                //获取主键的名称
                if ("COLUMN_NAME".equalsIgnoreCase(pkmd.getColumnName(i))) {
                    primaryKeyList.add(primaryKeys.getString(i));
                }
            }
        }
        return primaryKeyList;
    }

    /**
     * @param tableNameVal
     * @param connection
     * @return
     */
    private static Map<String, String> getColumnComment(String databaseType, String tableNameVal, Connection connection) {

        PreparedStatement columnListPst = null;
        ResultSet columnListRs = null;

        String sql = "";
        if ("mysql".equals(databaseType)) {
            sql = "show full columns from `" + tableNameVal + "`";
        } else if ("postgresql".equals(databaseType)) {

            String[] tableNameArr = tableNameVal.split("\\.");

            if (tableNameArr.length == 1) {
                tableNameVal = tableNameArr[0];
            } else {
                tableNameVal = tableNameArr[1];
            }

            sql = "SELECT" +
                    " A.attname AS \"Field\"," +
                    " col_description ( A.attrelid, A.attnum ) AS \"Comment\"" +
                    " FROM" +
                    " pg_class AS C," +
                    " pg_attribute AS A " +
                    " WHERE" +
                    " C.relname = '" + tableNameVal + "' " +
                    " AND A.attrelid = C.oid " +
                    " AND A.attnum > 0";
        } else if ("oracle".equals(databaseType)) {
            sql = "select column_name AS \"Field\",comments AS \"Comment\" from user_col_comments where lower(table_Name)='" + tableNameVal.toLowerCase() + "'";
        }

        // 列名集合
        Map<String, String> commentMqp = new HashMap<>();
        try {
            columnListPst = connection.prepareStatement(sql);
            columnListRs = columnListPst.executeQuery();

            while (columnListRs.next()) {

                commentMqp.put(columnListRs.getString("Field").toUpperCase(), columnListRs.getString("Comment"));

            }

        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ignored) {
            }
            try {
                if (columnListPst != null) {
                    columnListPst.close();
                }
            } catch (SQLException ignored) {
            }
            try {
                if (columnListRs != null) {
                    columnListRs.close();
                }
            } catch (SQLException ignored) {

            }
        }
        return commentMqp;
    }

    public static List<String> getColumnNameList(String databaseType, String tableNameVal, Connection connection) {

        PreparedStatement columnListPst = null;
        ResultSet columnListRs = null;

        String sql = "";
        if ("mysql".equals(databaseType)) {
            sql = "select * from `" + tableNameVal + "` where 1=0";
        } else if ("oracle".equals(databaseType)) {
            sql = "select * from \"" + tableNameVal + "\" where 1=0";
        } else if ("postgresql".equals(databaseType)) {
            sql = "select * from " + tableNameVal + " where 1=0";
        }

        // 列名集合
        List<String> columnList = new ArrayList<>();

        try {
            columnListPst = connection.prepareStatement(sql);
            columnListRs = columnListPst.executeQuery();
            ResultSetMetaData metaData = columnListRs.getMetaData();

            int columnCount = metaData.getColumnCount();

            for (int i = 0; i < columnCount; i++) {
                columnList.add(metaData.getColumnName(i + 1));
            }

        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListPst != null) {
                    columnListPst.close();
                }
            } catch (SQLException e) {
            }
            try {
                if (columnListRs != null) {
                    columnListRs.close();
                }
            } catch (SQLException e) {

            }
        }
        return columnList;
    }


    /**
     * 获取表中所有字段名称和类型
     *
     * @return
     */
    public static List<TableNameAndType> getColumnNameAndTypes(String databaseType, String tableNameVal,
                                                               Connection connection) {
        PreparedStatement columnListPst = null;
        ResultSet columnListRs = null;
        String sql = "";
        if ("mysql".equals(databaseType)) {
            sql = "select * from `" + tableNameVal + "` where 1=0";
        } else if ("oracle".equals(databaseType)) {
            sql = "select * from \"" + tableNameVal + "\" where 1=0";
        } else if ("postgresql".equals(databaseType)) {
            sql = "select * from " + tableNameVal + " where 1=0";
        }
        // 列名集合
        List<TableNameAndType> nameAndTypes = new ArrayList<>();
        try {
            columnListPst = connection.prepareStatement(sql);
            columnListRs = columnListPst.executeQuery();
            ResultSetMetaData metaData = columnListRs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                TableNameAndType nameAndType = new TableNameAndType();
                nameAndType.setColName(metaData.getColumnName(i + 1));
                //设置sqlParamName
                nameAndType.setEntityName(DataUtils.getSqlParam(metaData.getColumnName(i + 1)));
                nameAndType.setComment((metaData.getColumnName(i + 1)));
                String javaTypeName = "";
                String javaClassName = "";
                String columnTypeName = metaData.getColumnTypeName(i + 1).toUpperCase();
                switch (databaseType) {
                    case "postgresql":
                        switch (columnTypeName) {
                            case "VARCHAR":
                                javaTypeName = "String";
                                break;
                            case "bigint":
                                javaTypeName = "Long";
                                break;
                            case "CHAR":
                                javaTypeName = "String";
                                break;
                            case "INT2":
                                javaTypeName = "Integer";
                                break;
                            case "INT4":
                                javaTypeName = "Integer";
                                break;
                            case "INT8":
                                javaTypeName = "Long";
                                break;
                            case "SERIAL2":
                                javaTypeName = "Integer";
                                break;
                            case "SERIAL4":
                                javaTypeName = "Integer";
                                break;
                            case "SERIAL8":
                                javaTypeName = "Long";
                                break;
                            case "TEXT":
                                javaTypeName = "String";
                                break;
                            case "DATE":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIME":
                                javaTypeName = "Time";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIMETZ":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIMESTAMP":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIMESTAMPTZ":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            default:
                                javaTypeName = "String";
                                break;
                        }
                        break;
                    case "mysql":
                        switch (columnTypeName) {
                            case "VARCHAR":
                                javaTypeName = "String";
                                break;
                            case "BIGINT UNSIGNED":
                                javaTypeName = "Long";
                                break;
                            case "INT":
                                javaTypeName = "Integer";
                                break;
                            case "CHAR":
                                javaTypeName = "String";
                                break;
                            case "BLOB":
                                javaTypeName = "byte[]";
                                break;
                            case "TEXT":
                                javaTypeName = "String";
                                break;
                            case "INTEGER":
                                javaTypeName = "Long";
                                break;
                            case "TINYINT":
                                javaTypeName = "Integer";
                                break;
                            case "SMALLINT":
                                javaTypeName = "Integer";
                                break;
                            case "MEDIUMINT":
                                javaTypeName = "Integer";
                                break;
                            case "BIT":
                                javaTypeName = "Long";
                                break;
                            case "BIGINT":
                                javaTypeName = "Long";
                                break;
                            case "FLOAT":
                                javaTypeName = "Float";
                                break;
                            case "DOUBLE":
                                javaTypeName = "Double";
                                break;
                            case "DECIMAL":
                                javaTypeName = "BigDecimal";
                                javaClassName = "java.math.BigDecimal";
                                break;
                            case "BOOLEAN":
                                javaTypeName = "Integer";
                                break;
                            // 主键
                            case "ID":
                                javaTypeName = "Long";
                                break;
                            case "DATE":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIME":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "DATETIME":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIMESTAMP":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "YEAR":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            default:
                                javaTypeName = "String";
                                break;
                        }
                        break;
                    case "oracle":
                        switch (columnTypeName) {
                            case "NUMBER":
                                javaTypeName = "Integer";
                                break;
                            case "DATE":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            case "TIMESTAMP":
                                javaTypeName = "Date";
                                javaClassName = "java.util.Date";
                                break;
                            default:
                                javaTypeName = "String";
                                break;
                        }
                    default:
                        break;
                }
                nameAndType.setEntityParamType(javaTypeName);
                nameAndType.setClassName(javaClassName);
                nameAndTypes.add(nameAndType);
            }
            // 注释map
            Map<String, String> columnComment = getColumnComment(databaseType, tableNameVal, connection);
            for (TableNameAndType tableNameAndType : nameAndTypes) {
                String name = tableNameAndType.getColName().toUpperCase();
                // 设置注释内容
                assert columnComment != null;
                tableNameAndType.setComment(
                        "".equals(columnComment.get(name)) || columnComment.get(name) == null ? tableNameAndType.getColName() : columnComment.get(name));
            }
        } catch (SQLException e) {
            return null;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ignored) {
            }
            try {
                if (columnListPst != null) {
                    columnListPst.close();
                }
            } catch (SQLException ignored) {
            }
            try {
                if (columnListRs != null) {
                    columnListRs.close();
                }
            } catch (SQLException ignored) {

            }
        }
        return nameAndTypes;
    }

}
